package ar.gov.hackaton.dao;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import ar.gov.hackaton.model.CensoHogar;
import ar.gov.hackaton.model.InfoCategoria;
import ar.gov.hackaton.model.ProvinciaInfoCategoria;

@Repository
public class CensoHogarDao {

	@PersistenceContext
	private EntityManager entityManager;

	public CensoHogar find(Long id) {
		return entityManager.find(CensoHogar.class, id);
	}

	@SuppressWarnings("unchecked")
	public List<CensoHogar> listCensoHogar() {
		return entityManager.createQuery("select p from CensoHogar p").getResultList();
	}

	public CensoHogar getCensoHogar(Long provinciaId) {
		return (CensoHogar) entityManager.createQuery("select p from CensoHogar p where p.provinciaId=?")
				.setParameter(1, provinciaId).getSingleResult();
	}

	public ProvinciaInfo getCensoHogarByProvinciaId(Long provinciaId, String categoryName) {
		Query query = entityManager.createQuery(
				"select sum(p." + categoryName + ") from CensoHogar p where p.provinciaId=? group by provinciaId")
				.setParameter(1, provinciaId);
		Long suma = (Long) query.getSingleResult();
		return new ProvinciaInfo(provinciaId, suma);
	}
	
	public List<ProvinciaInfo> listCensoHogarProvincias(String categoryName) {
		List<ProvinciaInfo> result = new ArrayList<ProvinciaInfo>();
		Query query = entityManager.createQuery(
				"select c.provinciaId, ROUND((sum(c." + categoryName + ")*100)/sum(c.poblacion.total)) from CensoHogar c group by c.provinciaId");
		for (Object sumaByProvinciaElem : query.getResultList()) {
			Object[] sumaByProvincia = (Object[]) sumaByProvinciaElem;
			result.add(new ProvinciaInfo((Long) sumaByProvincia[0],(Long)  sumaByProvincia[1]));
		}
		return result;
	}

	@Transactional
	public CensoHogar save(CensoHogar censoHogar) {
		if (censoHogar.getId() == null) {
			entityManager.persist(censoHogar);
			return censoHogar;
		} else {
			return entityManager.merge(censoHogar);
		}
	}
	
	public ProvinciaInfoCategoria listCensoHogarProvinciasData(String categoria) {
		ProvinciaInfoCategoria result = new ProvinciaInfoCategoria();
		String hql = "";
		if (categoria.equals("vivienda"))
			hql = "SELECT c.provincia, ROUND((SUM(c.cantCasa)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantRancho)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantCasilla)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantDepartamento)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantPiezaInquilinato)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantHotel)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantLocal)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantMovil)*100)/sum(c.poblacion.total)), ROUND((SUM(c.cantCalle)*100)/sum(c.poblacion.total)) from CensoHogar c GROUP BY c.provinciaId";
		else if (categoria.equals("provisionAgua"))
			hql = "SELECT c.provincia, ROUND((SUM(c.aguaBeberBombaMotor)*100)/sum(c.poblacion.total)), ROUND((SUM(c.aguaBeberBombaManual)*100)/sum(c.poblacion.total)), ROUND((SUM(c.aguaBeberPozo)*100)/sum(c.poblacion.total)), ROUND((SUM(c.aguaBeberCisterna)*100)/sum(c.poblacion.total)), ROUND((SUM(c.aguaBeberRio)*100)/sum(c.poblacion.total)) from CensoHogar c GROUP BY c.provinciaId";
		Query query = entityManager.createQuery(hql);
		for (Object sumaByProvinciaElem : query.getResultList()) {
			Object[] sumaByProvincia = (Object[]) sumaByProvinciaElem;
			List<Long> valores = new LinkedList<Long>();
			for (int i = 1; i < sumaByProvincia.length; i++) {
				valores.add((Long)sumaByProvincia[i]);
			}
			result.getInfo().add(new InfoCategoria((String) sumaByProvincia[0], valores.toArray(new Long[0])));
		}
		return result;
	}
}
